using DevExpress.DocumentServices.ServiceModel.DataContracts;
using DevExpress.Map.Dashboard;
using DevExpress.Utils;
using DevExpress.Xpo.DB.Helpers;
using DevExpress.XtraCharts.Design;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;
using DocumentFormat.OpenXml.Office2010.CustomUI;
using DocumentFormat.OpenXml.Spreadsheet;
using LSQL;
using Shopfloormanagement;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
//using DevExpress.DataProcessing.InMemoryDataProcessor;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.TextBox;

namespace Shopfloormanagement
{
    public partial class CIPAdd : DevExpress.XtraEditors.XtraForm
    {
        private int ID = 0; // 新增项目ID
        private int[] SavingID = new int[6]; // savingID
        private int originID, typeID, CIPLevelID, ILID, departmentID, savingCalculationStatusID; // 各种下拉框ID
        public static bool isEdit; //判定是新增项目还是编辑项目
        public static bool isDeviation; //判定是新增项目且属于Deviation转入
        private bool isDirty = false;//内容变更检测+智能关闭提示使用
        private bool isILDirty = false;//内容变更检测+智能关闭提示使用+判断是否更新服务器中的IL统计数据
        private bool isCIPLevelDirty = false;//内容变更检测+智能关闭提示使用+判断是否更新服务器中的IL统计数据


        // 选中项目ID
        public static int SelectPID = 0;
        public static int TopicID = 0;
        public static CIPAdd instance = new CIPAdd();

        public CIPAdd()
        {
            instance = this;
            InitializeComponent();
            // 关键：让窗体优先接收键盘事件,F12隐藏按键使用
            this.KeyPreview = true;

            gridView1.RowHeight = 60;
            this.WindowState = FormWindowState.Maximized;
        }

        private void CIPAdd_Load(object sender, EventArgs e)
        {
            xtraTabControl1.ShowTabHeader = DevExpress.Utils.DefaultBoolean.False;

            // 统一初始化 ComboBox
            InitComboBox(comboBoxIL, "CIP_IL", "IL", "ID");
            InitComboBox(comboBoxOrigin, "CIP_origin", "origin", "ID");
            InitComboBox(comboBoxType, "CIP_type", "type", "ID");
            InitComboBox(comboBoxCIPLevel, "CIP_level", "CIPLevel", "ID");
            //InitComboBox(comboBoxSubDept, "CIP_subDepartment", "subDepartment", "ID");
            InitComboBox(comboBoxCalcutation, "CIP_savingCalculationStatus", "savingCalculationStatus", "ID");
            //department初始化方法不一样，有额外筛选条件deptlevel=4
            InitComboBoxForDept(comboBoxDept, "Departments", "DepartmentName", "ID");
            //ToolTip

            toolTipController1.SetToolTip(labelControl18, "No need to fill in. It will be automatically generated after clicking \"Save\" and serves as the project's unique identification number.");
            toolTipController1.SetToolTip(labelControl12, "L1:The project's savings amount is less than 300K and can be completed through internal department coordination\nL2:The project's savings amount is less than 300K and requires cross-departmental coordination to complete\nL3:The project's savings amount is 300K or more");
            toolTipController1.SetToolTip(labelControl14, "IL1:Idea Registered\nIL2:Idea being investigated\nIL3:Idea cleared for decision\nIL4:Positive decision for implementation\nIL5:Project team is working according to a timing plan\nIL6:Idea Implemented and financially effective\nILX:Idea Rejected");
            toolTipController1.SetToolTip(labelControl13, "No need to fill in. The current date will be automatically saved after clicking \"Save\", and it cannot be modified.");
            toolTipController1.SetToolTip(labelControl15, "IL6 planned start date, It can be modified, but the date of the first save will be stored in \"Initial IL6 due date\" and cannot be changed.");
            toolTipController1.SetToolTip(labelFirstIL6DueDate, "No need to fill in. The \"IL6 due date\" from the first save will be synced here automatically, and it cannot be changed afterward.");
            toolTipController1.SetToolTip(labelControl16, "Actual IL6 start date, It can only be filled in when the Implement level reaches IL6.");
            toolTipController1.SetToolTip(labelControl9, "No need to fill in. It is determined by the Sub_department");
            toolTipController1.SetToolTip(labelControl3, "When the project's savings calculation reaches 12 full months, please change this item to \"Completed\". Only then will the project's Status change to \"Completed\".");








            //节约计算状态设为未完成
            comboBoxCalcutation.SelectedIndex = 0;
            // 设置不可编辑区域
            SetReadOnlyFields();

            // 初始化表格
            InitGridView();
            //判断是新增还是编辑
            if (isEdit)
            {
                //1载入基本信息
                DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", $"delTag=0 and [PID]={SelectPID} order by PID");

                if (dt.Rows.Count > 0)
                {
                    textProject_ID.Text = SelectPID.ToString();
                    textTopic.Text = dt.Rows[0]["Topic"].ToString();

                    comboBoxOrigin.Text = dt.Rows[0]["origin"].ToString();
                    originID = Convert.ToInt32(dt.Rows[0]["originID"]);

                    comboBoxType.Text = dt.Rows[0]["type"].ToString();
                    typeID = Convert.ToInt32(dt.Rows[0]["typeID"]);

                    comboBoxIL.Text = dt.Rows[0]["IL"].ToString();
                    ILID = Convert.ToInt32(dt.Rows[0]["ILID"]);

                    comboBoxCIPLevel.Text = dt.Rows[0]["CIPLevel"].ToString();
                    CIPLevelID = Convert.ToInt32(dt.Rows[0]["levelID"]);

                    //comboBoxSubDept.Text = dt.Rows[0]["subDepartment"].ToString();
                    departmentID = Convert.ToInt32(dt.Rows[0]["departmentID"]);
                    comboBoxDept.Text = dt.Rows[0]["DepartmentName"].ToString();
                    //textProcess.Text = dt.Rows[0]["department"].ToString();

                    textOwner.Text = dt.Rows[0]["owner"].ToString();
                    textComments.Text = dt.Rows[0]["comments"].ToString();
                    textInitiator.Text = dt.Rows[0]["initiator"].ToString();

                    comboBoxCalcutation.Text = dt.Rows[0]["savingCalculationStatus"].ToString();
                    savingCalculationStatusID = Convert.ToInt32(dt.Rows[0]["savingCalculationStatusID"]);

                    //只显示日期，且防止空值报错
                    object FirstIL6DueDateValue = dt.Rows[0]["IL6FirstDueDate"];
                    if (FirstIL6DueDateValue != DBNull.Value)
                    {
                        textFirstIL6DueDate.EditValue = Convert.ToDateTime(FirstIL6DueDateValue).ToString("yyyy/M/dd");
                    }
                    else
                    {
                        textFirstIL6DueDate.EditValue = null;
                    }
                    object createDateValue = dt.Rows[0]["createDate"];
                    if (createDateValue != DBNull.Value)
                    {
                        dateCreateDate.EditValue = Convert.ToDateTime(createDateValue).Date;
                    }
                    else
                    {
                        dateCreateDate.EditValue = null;
                    }
                    object IL6CurrentDueDateValue = dt.Rows[0]["IL6CurrentDueDate"];
                    if (IL6CurrentDueDateValue != DBNull.Value)
                    {
                        dateIL6DueDate.EditValue = Convert.ToDateTime(IL6CurrentDueDateValue).Date;
                    }
                    else
                    {
                        dateIL6DueDate.EditValue = null;
                    }
                    object dateActualIL6dateValue = dt.Rows[0]["IL6ActualDuedate"];
                    if (dateActualIL6dateValue != DBNull.Value)
                    {
                        dateActualIL6date.EditValue = Convert.ToDateTime(dateActualIL6dateValue).Date;
                    }
                    else
                    {
                        dateActualIL6date.EditValue = null;
                    }

                    object measureIDValue = dt.Rows[0]["measureID"];
                    if (measureIDValue != DBNull.Value)
                    {
                        textMeasure_ID.Text = measureIDValue.ToString();
                    }
                    else
                    {
                        textMeasure_ID.Text = null;
                    }




                    //2载入Saving信息
                    DataTable dt1 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_saving", $"delTag=0 and [PID]={SelectPID} order by ID");
                    //给SavingID赋值，作为编辑而不是插入的依据
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        SavingID[i] = Convert.ToInt32(dt1.Rows[i]["ID"]);
                    }

                    dt1.Columns.Add("Total", typeof(decimal));
                    gridControl1.DataSource = dt1;
                    gridView1.Columns.Clear();
                    gridView1.Columns.AddVisible("savingType", "Saving type");
                    gridView1.Columns.AddVisible("year", "Year");
                    gridView1.Columns.AddVisible("1", "Jan");
                    gridView1.Columns.AddVisible("2", "Feb");
                    gridView1.Columns.AddVisible("3", "Mar");
                    gridView1.Columns.AddVisible("4", "Apr");
                    gridView1.Columns.AddVisible("5", "May");
                    gridView1.Columns.AddVisible("6", "Jun");
                    gridView1.Columns.AddVisible("7", "July");
                    gridView1.Columns.AddVisible("8", "Aug");
                    gridView1.Columns.AddVisible("9", "Sep");
                    gridView1.Columns.AddVisible("10", "Oct");
                    gridView1.Columns.AddVisible("11", "Nov");
                    gridView1.Columns.AddVisible("12", "Dec");
                    gridView1.Columns.AddVisible("Total", "Total");
                    CalculateTotal();
                }
                else
                {
                    MessageBox.Show("Failed to load project information", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            //内容变更检测+智能关闭提示
            textTopic.TextChanged += (s, e1) => isDirty = true;
            comboBoxOrigin.SelectedIndexChanged += (s, e7) => isDirty = true;
            comboBoxType.SelectedIndexChanged += (s, e8) => isDirty = true;

            comboBoxCIPLevel.SelectedIndexChanged += (s, e3) => isDirty = true;
            comboBoxCIPLevel.SelectedIndexChanged += (s, e3) => isCIPLevelDirty = true;

            comboBoxIL.SelectedIndexChanged += (s, e4) => isDirty = true;
            comboBoxIL.SelectedIndexChanged += (s, e4) => isILDirty = true;

            dateIL6DueDate.EditValueChanged += (s, e5) => isDirty = true;
            dateActualIL6date.EditValueChanged += (s, e6) => isDirty = true;
            textOwner.TextChanged += (s, e9) => isDirty = true;
            textInitiator.TextChanged += (s, e13) => isDirty = true;

            comboBoxSubDept.SelectedIndexChanged += (s, e2) => isDirty = true;
            textComments.TextChanged += (s, e10) => isDirty = true;
            comboBoxCalcutation.SelectedIndexChanged += (s, e11) => isDirty = true;
            gridView1.CellValueChanged += (s, e12) => isDirty = true;
            textMeasure_ID.TextChanged += (s, e13) => isDirty = true;

        }

        /// <summary>
        /// 通用方法：初始化ComboBox
        /// </summary>
        private void InitComboBox(ComboBoxEdit comboBox, string tableName, string displayMember, string valueMember)
        {
            DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, tableName, "delTag=0 order by ID");
            if (dt.Rows.Count > 0)
            {
                publicFunc.ComboBoxInit(dt, comboBox, displayMember, valueMember);
            }
        }
        private void InitComboBoxForDept(ComboBoxEdit comboBox, string tableName, string displayMember, string valueMember)
        {
            DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, tableName, "Del_tag=0 and departmentLevel =4 and isDisplay =1 order by ID");
            if (dt.Rows.Count > 0)
            {
                publicFunc.ComboBoxInit(dt, comboBox, displayMember, valueMember);
            }
        }


        /// <summary>
        /// 初始化 GridView
        /// </summary>
        private void InitGridView()
        {
            // 让所有数据单元格水平 & 垂直居中
            gridView1.Appearance.Row.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
            gridView1.Appearance.Row.TextOptions.VAlignment = DevExpress.Utils.VertAlignment.Center;

            // 让所有标题（列头） 水平 & 垂直居中
            gridView1.Appearance.HeaderPanel.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
            gridView1.Appearance.HeaderPanel.TextOptions.VAlignment = DevExpress.Utils.VertAlignment.Center;

            // 添加自定义绘制事件
            gridView1.CustomDrawCell += GridView1_CustomDrawCell;
            
            // 添加显示编辑器前事件，用于控制单元格是否可编辑
            gridView1.ShowingEditor += GridView1_ShowingEditor;
            
            // 添加鼠标点击事件，用于在点击不可编辑区域时显示提示
            gridView1.MouseDown += GridView1_MouseDown;

            // 初始化 DataTable
            DataTable dt = new DataTable();
            dt.Columns.Add("Saving type");
            dt.Columns.Add("Year");
            dt.Columns.Add("Jan", typeof(decimal));
            dt.Columns.Add("Feb", typeof(decimal));
            dt.Columns.Add("Mar", typeof(decimal));
            dt.Columns.Add("Apr", typeof(decimal));
            dt.Columns.Add("May", typeof(decimal));
            dt.Columns.Add("Jun", typeof(decimal));
            dt.Columns.Add("Jul", typeof(decimal));
            dt.Columns.Add("Aug", typeof(decimal));
            dt.Columns.Add("Sep", typeof(decimal));
            dt.Columns.Add("Oct", typeof(decimal));
            dt.Columns.Add("Nov", typeof(decimal));
            dt.Columns.Add("Dec", typeof(decimal));
            dt.Columns.Add("Total", typeof(decimal));
            



            int thisYear = DateTime.Now.Year;
            string[] savingTypes = { "Planned", "Planned", "Actual", "Actual" };
            for (int i = 0; i < 4; i++)
            {
                DataRow row = dt.NewRow();
                row["Saving type"] = savingTypes[i];
                row["Year"] = thisYear + (i % 2);
                row["Total"] = 0.00;
                dt.Rows.Add(row);
            }

            

            gridControl1.DataSource = dt;
            gridView1.OptionsView.ShowIndicator = false;

            // 前两列不可编辑
            gridView1.Columns[0].OptionsColumn.AllowEdit = false;
            //允许编辑（输入旧项目用）
            //gridView1.Columns[1].OptionsColumn.AllowEdit = false;
            // 禁用排序
            gridView1.OptionsCustomization.AllowSort = false;
            gridView1.OptionsCustomization.AllowFilter = false;

            // 添加单元格值变化事件处理
            gridView1.CellValueChanged += GridView1_CellValueChanged;
        }

        private void GridView1_CustomDrawCell(object sender, DevExpress.XtraGrid.Views.Base.RowCellCustomDrawEventArgs e)
        {
            // 只处理数值类型的列（从Jan到Total）
            if (e.Column.FieldName != "Saving type" && e.Column.FieldName != "Year" && e.Column.FieldName != "Total")
            {
                if (e.DisplayText == "0" || e.DisplayText == "0.00")
                {
                    e.Appearance.ForeColor = System.Drawing.Color.White;
                }
            }
        }

        /// <summary>
        /// 计算并更新Total值
        /// </summary>
        private void CalculateTotal()
        {
            DataTable dt = (DataTable)gridControl1.DataSource;
            if (dt == null || dt.Rows.Count < 2) return;

            //budget
            decimal total = 0;
            // 计算第一行（Budget）的总和
            for (int col = 2; col <= 13; col++)
            {
                object value = dt.Rows[0][col];
                total += value == DBNull.Value ? 0 : Convert.ToDecimal(value);
            }
            // 计算第二行（Budget）的总和
            for (int col = 2; col <= 13; col++)
            {
                object value = dt.Rows[1][col];
                total += value == DBNull.Value ? 0 : Convert.ToDecimal(value);
            }

            dt.Rows[1]["Total"] = total;
            //actal
            decimal total1 = 0;
            // 计算第一行的总和
            for (int col = 2; col <= 13; col++)
            {
                object value = dt.Rows[2][col];
                total1 += value == DBNull.Value ? 0 : Convert.ToDecimal(value);
            }
            // 计算第二行的总和
            for (int col = 2; col <= 13; col++)
            {
                object value = dt.Rows[3][col];
                total1 += value == DBNull.Value ? 0 : Convert.ToDecimal(value);
            }

            dt.Rows[3]["Total"] = total1;


        }

        private void GridView1_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            if (e.Column.FieldName != "Total")  // 避免在更新Total时触发递归
            {
                CalculateTotal();
            }
        }

        /// <summary>
        /// 设置只读字段
        /// </summary>
        private void SetReadOnlyFields()
        {
            textProject_ID.ReadOnly = true;
            textFirstIL6DueDate.ReadOnly = true;
            //textDept.ReadOnly = true;
            textProcess.ReadOnly = true;
            dateCreateDate.ReadOnly = true;
            dateCreateDate.Properties.Buttons.Clear();
            dateActualIL6date.ReadOnly = true;
            //dateActualIL6date.Properties.Buttons.Clear();
        }

        private void ButtonSave_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(textTopic.Text))
            {
                MessageBox.Show("Topic cannot be empty", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (string.IsNullOrWhiteSpace(comboBoxDept.Text))
            {
                MessageBox.Show("Department cannot be empty", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (string.IsNullOrWhiteSpace(comboBoxCIPLevel.Text))
            {
                MessageBox.Show("CIP level cannot be empty", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (string.IsNullOrWhiteSpace(comboBoxIL.Text))
            {
                MessageBox.Show("Implement Level cannot be empty", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            DateTime now = DateTime.Now;
            string IL6CurrentDueDate = dateIL6DueDate.EditValue == null ? "NULL" : $"'{dateIL6DueDate.DateTime:yyyy-MM-dd HH:mm:ss}'";
            string IL6ActualDuedate = dateActualIL6date.EditValue == null ? "NULL" : $"'{dateActualIL6date.DateTime:yyyy-MM-dd HH:mm:ss}'";

            if (string.IsNullOrWhiteSpace(textProject_ID.Text)) // 新项目
            {
                // 获取当前最大的PID值,不筛选deltag，生成项目ID时，删除的项目也考虑
                //CIP list中PID列作为项目编号显示，ID会跳号，进作为代码中的查询ID
                DataTable dtMaxPID = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_list", "(delTag=0 or delTag=1)");
                int maxPID = 0;
                if (dtMaxPID.Rows.Count > 0)
                {
                    maxPID = Convert.ToInt32(dtMaxPID.Compute("MAX(PID)", ""));
                }
                int newPID = maxPID + 1;

                string columns = "topic, originID, typeID, levelID, ILID, createDate, IL6CurrentDueDate, IL6FirstDueDate, IL6ActualDuedate, owner, departmentID, comments, savingCalculationStatusID, PID,initiator,measureID";
                string values = $"'{textTopic.Text}', {originID}, {typeID}, {CIPLevelID}, {ILID}, '{now:yyyy-MM-dd HH:mm:ss}', {IL6CurrentDueDate}, {IL6CurrentDueDate}, {IL6ActualDuedate}, '{textOwner.Text}', {departmentID}, '{textComments.Text}', {savingCalculationStatusID}, {newPID}, '{textInitiator.Text}', '{textMeasure_ID.Text}'";

                ID = LSQL.SQL.InsertReturnID(SystemInfo.SQL, "CIP_list", columns, values);
                textProject_ID.Text = newPID.ToString();
                //新增后，则该ID为选中ID
                CIPAdd.SelectPID = newPID;
                //生成报告用
                projectReport.reportID=newPID;
                dateCreateDate.EditValue = now;
                textFirstIL6DueDate.Text = dateIL6DueDate.EditValue == null ? "" : $"{dateIL6DueDate.DateTime:yyyy/M/dd}";

                if (ID > 0) SaveOrUpdateSavingData(true);
            }
            else // 更新已有项目
            {
                string updateContent = $"topic='{textTopic.Text}', originID={originID}, typeID={typeID}, levelID={CIPLevelID}, ILID={ILID}, IL6CurrentDueDate={IL6CurrentDueDate}, IL6ActualDuedate={IL6ActualDuedate}, owner='{textOwner.Text}', departmentID={departmentID}, comments='{textComments.Text}',savingCalculationStatusID={savingCalculationStatusID}, initiator='{textInitiator.Text}', measureID='{textMeasure_ID.Text}'";
                string condition = $"[PID]={textProject_ID.Text} and delTag=0";

                if (LSQL.SQL.Update(SystemInfo.SQL, "CIP_list", updateContent, condition)) SaveOrUpdateSavingData(SavingID[0] == 0);
            }
            //IL变化则往服务器里更新统计数据
            if (isILDirty|| isCIPLevelDirty)
            {
                UpdateILCounts(true);
                UpdateILCounts(false);
            }
            //智能保存用
            isDirty = false;
            isILDirty = false;
            isCIPLevelDirty = false;


        }

        /// <summary>
        /// 保存或更新 Saving 数据
        /// </summary>
        private void SaveOrUpdateSavingData(bool isInsert)
        {
            bool success = true;
            for (int row = 0; row <= 3; row++)
            {
                int year = Convert.ToInt32(gridView1.GetRowCellValue(row, gridView1.Columns[1]));
                decimal[] cellValues = new decimal[12];
                for (int col = 2; col <= 13; col++)
                {
                    object cellObj = gridView1.GetRowCellValue(row, gridView1.Columns[col]);
                    cellValues[col - 2] = cellObj == null || cellObj == DBNull.Value ? 0 : Convert.ToDecimal(cellObj);
                }

                if (isInsert)
                {
                    //前三行savingtype是1，budget
                    if (row <= 1)
                    {
                        string columns = "[savingType],[year],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[CIPID]";
                        string values = $"'Budget',{year},{string.Join(",", cellValues)},{ID}";
                        SavingID[row] = LSQL.SQL.InsertReturnID(SystemInfo.SQL, "CIP_saving", columns, values);
                        success &= SavingID[row] > 0;
                    }
                    //后三行savingtype是0，actual
                    else
                    {
                        string columns = "[savingType],[year],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[CIPID]";
                        string values = $"'Actual',{year},{string.Join(",", cellValues)},{ID}";
                        SavingID[row] = LSQL.SQL.InsertReturnID(SystemInfo.SQL, "CIP_saving", columns, values);
                        success &= SavingID[row] > 0;
                    }
                }
                else
                {
                    //更新时不存储实际节约的值，但是存储实际年份
                    if (row <= 1)
                    {
                        string updateContent = $"year={year}, " + string.Join(", ", cellValues.Select((v, i) => $"[{i + 1}]={v}"));
                        string condition = $"[ID]={SavingID[row]} and delTag=0";
                        success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_saving", updateContent, condition);
                    }
                    //只存储实际年份
                    else
                    {
                        string updateContent = $"year={year}" ;
                        string condition = $"[ID]={SavingID[row]} and delTag=0";
                        success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_saving", updateContent, condition);
                    }

                }
            }
            //单独写入第二行Total金额
            decimal BudgetSaving = Convert.ToDecimal(gridView1.GetRowCellValue(1, gridView1.Columns[14]));
            string updateContent1 = $"budgetSaving={BudgetSaving}";
            string condition1 = $"[PID]={textProject_ID.Text} and delTag=0";
            success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_list", updateContent1, condition1);

            if (TopicID != 0 && isDeviation)
            {
                bool toCIP = SQL.Update(SystemInfo.SQL, "Topic", "isCIP = 1", $"ID = {TopicID}");
            }
            
            MessageBox.Show(success ? "Save successful" : "Save failed", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

        //读取comboxID的方法
        private int cboxID(DevExpress.XtraEditors.ComboBoxEdit comboBox)

        {
            int ID = 0;
            var selectedItem = comboBox.SelectedItem as publicFunc.ComboBoxItem;
            if (selectedItem != null)
            {
                var tag = selectedItem.Tag; // 这里可以使用tag
                ID = Convert.ToInt32(tag);

            }
            return ID;
        }
        private void xtraTabPage1_Paint(object sender, PaintEventArgs e)
        {

        }
        private void comboBoxSubDept_SelectedIndexChanged(object sender, EventArgs e)
        {
            //if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            //{
            //    SubDepartmentID = cboxID(comboBox);
            //}
            ////部门层级联动
            //DataTable dt5 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_subDepartment", $"delTag=0 and [subDepartment]='{comboBoxSubDept.Text}' order by ID");
            //textDept.Text = dt5.Rows[0][2].ToString();
            //textProcess.Text = dt5.Rows[0][3].ToString();

        }

        private void comboBoxOrigin_SelectedValueChanged(object sender, EventArgs e)
        {
            if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            {
                originID = cboxID(comboBox);
            }
        }

        private void btnReport_Click(object sender, EventArgs e)
        {
           
            Form form = new projectReport();
            form.ShowDialog();
        }

        private void btnSavingCalculate_Click(object sender, EventArgs e)
        {
            if (textProject_ID.Text != "")
            {
                CostSavingLoader.calculateID = Convert.ToInt32(textProject_ID.Text);
                CostSavingLoader.isCalculate = true;
                CostSavingLoader.isOverview = false;
                CIPAdd.SelectPID = Convert.ToInt32(textProject_ID.Text);
                Form form = new CostSavingLoader();
                form.FormClosing += CostSavingLoader_FormClosing;
                form.ShowDialog();
            }
        }

        private void comboBoxCalcutation_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            {
                savingCalculationStatusID = cboxID(comboBox);
            }
        }

        private void comboBoxType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            {
                typeID = cboxID(comboBox);
            }
        }

        private void CIPAdd_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (isDirty)
            {
                DialogResult result = MessageBox.Show("Do you want to save updates?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

                if (result == DialogResult.Yes)
                {
                    ButtonSave_Click(null, EventArgs.Empty);
                    // 允许关闭
                }
                else if (result == DialogResult.No)
                {
                    MessageBox.Show("Not saved!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    // 允许关闭
                }
                else
                {
                    // 取消关闭
                    e.Cancel = true;
                }
            }
            // isDirty为false时，直接关闭
        }

        private void comboBoxCIPLevel_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            {
                CIPLevelID = cboxID(comboBox);
            }
        }

        private void comboBoxIL_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            {
                ILID = cboxID(comboBox);
                if (comboBoxIL.Text == "IL6")
                {
                    //也可手动选择
                    dateActualIL6date.ReadOnly = false;
                }
                else
                {
                    dateActualIL6date.EditValue = null;
                    dateActualIL6date.ReadOnly = true;

                }
            }

        }

        private void btnMPP_Click(object sender, EventArgs e)
        {
            if (textProject_ID.Text != "")
            {
                Form form = new MPP();
                MPP.CurrentPID = Convert.ToInt32(textProject_ID.Text);
                form.ShowDialog();

            }



        }

        /// <summary>
        /// 检查单元格是否在禁止编辑的范围内
        /// </summary>
        /// <param name="rowHandle">行索引</param>
        /// <param name="column">列对象</param>
        /// <returns>是否在禁止编辑范围内</returns>
        private bool IsInProtectedRange(int rowHandle, DevExpress.XtraGrid.Columns.GridColumn column)
        {
            // 检查行是否在2-3行范围内
            if (rowHandle >= 2 && rowHandle <= 3)
            {
                // 检查列是否在2-14列范围内
                int columnIndex = gridView1.Columns.IndexOf(column);
                if (columnIndex >= 2 && columnIndex <= 14)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 控制单元格是否可编辑
        /// </summary>
        private void GridView1_ShowingEditor(object sender, System.ComponentModel.CancelEventArgs e)
        {
            if (IsInProtectedRange(gridView1.FocusedRowHandle, gridView1.FocusedColumn))
            {
                // 取消编辑操作
                e.Cancel = true;
            }
        }

        /// <summary>
        /// 鼠标点击事件处理，点击不可编辑区域时显示提示
        /// </summary>
        private void GridView1_MouseDown(object sender, MouseEventArgs e)
        {
            // 获取鼠标点击位置下的行和列信息
            DevExpress.XtraGrid.Views.Grid.ViewInfo.GridHitInfo hitInfo = gridView1.CalcHitInfo(e.Location);
            
            // 检查是否点击了数据单元格
            if (hitInfo.InRowCell)
            {
                // 检查单元格是否在禁止编辑范围内
                if (IsInProtectedRange(hitInfo.RowHandle, hitInfo.Column))
                {
                    // 显示提示信息
                    MessageBox.Show("Actual Saving here is for display only and cannot be edited. To edit Actual Saving, please click the 'Saving calculation' button above.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

        private void CIPAdd_KeyDown(object sender, KeyEventArgs e)
        {
            // 判断是否按下 F12 键
            if (e.KeyCode == Keys.F12)
            {
               textMeasure_ID.Visible = !textMeasure_ID.Visible;
                labelControl6.Visible = !labelControl6.Visible;
                e.Handled = true;
            }
        }

        private void dateActualIL6date_Click(object sender, EventArgs e)
        {
            if (comboBoxIL.Text == "IL6")
            {
                dateActualIL6date.ReadOnly = false;
            }
            else 
            {
                // 显示提示信息
                MessageBox.Show("Only projects that have entered IL6 can fill in 'Actual IL6 date'", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }


        }

        private void comboBoxOrigin_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void comboBoxDept_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (sender is DevExpress.XtraEditors.ComboBoxEdit comboBox)
            {
                departmentID = cboxID(comboBox);
            }

        }

        private void InitAllControls()
        {
            comboBoxOrigin.SelectedIndex = -1;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            // 简化btnClose_Click方法，直接调用Close()
            // 让FormClosing事件统一处理保存提示逻辑
            this.Close();
        }

        private void CostSavingLoader_FormClosing(object sender, FormClosingEventArgs e)
        {
            //刷新列表数据
            if(textProject_ID.Text.ToString() != "")
            {
                int CIPID = Convert.ToInt32(textProject_ID.Text);
                refreshActualSaving(CIPID);

            }
        }


        private void refreshActualSaving(int CIPID)
        
        {
            DataTable dt1 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_saving", $"delTag=0 and [PID]={CIPID} order by ID");
            dt1.Columns.Add("Total", typeof(decimal));
            gridControl1.DataSource = dt1;
            gridView1.Columns.Clear();
            gridView1.Columns.AddVisible("savingType", "Saving type");
            gridView1.Columns.AddVisible("year", "Year");
            gridView1.Columns.AddVisible("1", "Jan");
            gridView1.Columns.AddVisible("2", "Feb");
            gridView1.Columns.AddVisible("3", "Mar");
            gridView1.Columns.AddVisible("4", "Apr");
            gridView1.Columns.AddVisible("5", "May");
            gridView1.Columns.AddVisible("6", "Jun");
            gridView1.Columns.AddVisible("7", "July");
            gridView1.Columns.AddVisible("8", "Aug");
            gridView1.Columns.AddVisible("9", "Sep");
            gridView1.Columns.AddVisible("10", "Oct");
            gridView1.Columns.AddVisible("11", "Nov");
            gridView1.Columns.AddVisible("12", "Dec");
            gridView1.Columns.AddVisible("Total", "Total");
            
            // 重新绑定事件，因为列被清除了
            gridView1.CustomDrawCell += GridView1_CustomDrawCell;
            gridView1.ShowingEditor += GridView1_ShowingEditor;
            gridView1.MouseDown += GridView1_MouseDown;
            
            CalculateTotal();
        }
        public static void UpdateILCounts(bool isOnlyCIP23)
        {
            //调用时，SFML4和非L4，分别调用写入一遍，查询时用isOnlyCIP23区分标记
            string connectionString = SystemInfo.SQL;
            int newBatchNumber = 1; // 默认批次号为1
            
            // 从连接字符串中提取数据库名称
            string databaseName = "";
            string[] connectionParts = connectionString.Split(';');
            foreach (string part in connectionParts)
            {
                if (part.Trim().StartsWith("Database=", StringComparison.OrdinalIgnoreCase))
                {
                    databaseName = part.Trim().Substring(9);
                    break;
                }
            }
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    
                    // 获取当前最大的batch值并加1作为新的批次号
                    string getMaxBatchQuery = $"SELECT ISNULL(MAX(batch), 0) + 1 FROM [{databaseName}].[dbo].[CIP_ILData]";
                    using (SqlCommand getMaxBatchCommand = new SqlCommand(getMaxBatchQuery, connection))
                    {
                        newBatchNumber = (int)getMaxBatchCommand.ExecuteScalar();
                    }
                    
                    string sqlQuery = "";
                    if (isOnlyCIP23)
                    {
                        //SFM4用，筛层级:isOnlyCIP23
                        sqlQuery = $@"
            INSERT INTO [{databaseName}].[dbo].[CIP_ILData] (department, yearMonth, ILX, IL1, IL2, IL3, IL4, IL5, IL6, isOnlyCIP23, batch)
            SELECT 
                DepartmentName,
                DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS yearMonth,
                COUNT(CASE WHEN IL = 'ILX' THEN 1 END) AS ILX,
                COUNT(CASE WHEN IL = 'IL1' THEN 1 END) AS IL1,
                COUNT(CASE WHEN IL = 'IL2' THEN 1 END) AS IL2,
                COUNT(CASE WHEN IL = 'IL3' THEN 1 END) AS IL3,
                COUNT(CASE WHEN IL = 'IL4' THEN 1 END) AS IL4,
                COUNT(CASE WHEN IL = 'IL5' THEN 1 END) AS IL5,
                COUNT(CASE WHEN IL = 'IL6' THEN 1 END) AS IL6,
                1 AS isOnlyCIP23,
                @batchNumber AS batch
            FROM [{databaseName}].[dbo].[View_CIP_list]
            WHERE delTag = 0 AND (CIPLevel= 'L2' or CIPLevel= 'L3')
              AND createDate >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
            GROUP BY DepartmentName;
        ";

                    }
                    else
                    {
                        //SFM1-3用，不用筛CIP层级
                        // 修改SQL语句，统一结构，包含isOnlyCIP23字段并设置为0
                        sqlQuery = $@"
            INSERT INTO [{databaseName}].[dbo].[CIP_ILData] (department, yearMonth, ILX, IL1, IL2, IL3, IL4, IL5, IL6, isOnlyCIP23, batch)
            SELECT 
                DepartmentName,
                DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS yearMonth,
                COUNT(CASE WHEN IL = 'ILX' THEN 1 END) AS ILX,
                COUNT(CASE WHEN IL = 'IL1' THEN 1 END) AS IL1,
                COUNT(CASE WHEN IL = 'IL2' THEN 1 END) AS IL2,
                COUNT(CASE WHEN IL = 'IL3' THEN 1 END) AS IL3,
                COUNT(CASE WHEN IL = 'IL4' THEN 1 END) AS IL4,
                COUNT(CASE WHEN IL = 'IL5' THEN 1 END) AS IL5,
                COUNT(CASE WHEN IL = 'IL6' THEN 1 END) AS IL6,
                0 AS isOnlyCIP23,
                @batchNumber AS batch
            FROM [{databaseName}].[dbo].[View_CIP_list]
            WHERE delTag = 0
              AND createDate >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
            GROUP BY DepartmentName;
        ";

                    }
                    
                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        // 添加批次号参数
                        command.Parameters.AddWithValue("@batchNumber", newBatchNumber);
                        int rowsAffected = command.ExecuteNonQuery();
                        
                        // 检查是否有数据被插入
                        if (rowsAffected > 0)
                        {
                            Console.WriteLine("数据同步完成！批次号: " + newBatchNumber + ", 影响行数: " + rowsAffected);
                        }
                        else
                        {
                            Console.WriteLine("警告：没有数据被插入！批次号: " + newBatchNumber);
                            // 可以在这里抛出异常或返回错误标志
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("同步失败: " + ex.ToString()); // 输出完整异常信息
                    throw; // 重新抛出异常，让调用方知道发生了错误
                }
            }
        }


    }




}

















